package com.saas.sql;

public class BrandQuery
{
  public static String brand_SELECT_BRAND_BY_CLASSID = "select b.id,b.class_id,b.brand_name,bc.store_path,bc.article_rule  from brand b,brand_class bc where b.class_id like :VCLASS_ID and validity=1 and web_tag=1 order by  b.oper_time desc";
  public static String brand_QUERY_STORE_PATH_BY_CLASSID = "SELECT STORE_PATH FROM brand_class where class_id=:VCLASS_ID";
  public static String brand_SEL_ONE_BY_NONE = "SELECT MAX(class_id) as maxid FROM brand_class WHERE LENGTH(class_id)=3";
  public static String brand_SELECT_MAXID = "SELECT MAX(class_id) as maxid FROM brand_class WHERE LENGTH(class_id)=:VLENGTH AND class_id LIKE :VUP_ID";
  public static String brand_ADD_BRAND_CLASS = "insert into brand_class(class_id,class_name,class_desc,remove_tag,oper_user,list_model,index_model,content_model,store_path,list_rule,home_name,article_rule,dyna_show,oper_time)values(:VCLASS_ID,:VCLASS_NAME,:VCLASS_DESC,:VREMOVE_TAG,:VOPER_USER,:VLIST_MODEL,:VINDEX_MODEL,:VCONTENT_MODEL,:VSTOCE_PATH,:VLIST_RULE,:VHOME_NAME,:VARTICLE_RULE,:VDYNA_SHOW,now())";
  public static String brand_class_GET_BRAND_CLASS_NAME = "select class_id,class_name from brand_class where remove_tag = 1";
  public static String brand_SEL_ALL_ONE_BY_NONE = "SELECT * FROM brand_class WHERE LENGTH(class_id)=3 AND REMOVE_TAG='1' order by oper_time desc ";
  public static String brand_SELECT_SON_BY_UP = "SELECT * FROM brand_class WHERE LENGTH(class_id)=:VLENGTH  AND  class_id LIKE :VUP_ID AND REMOVE_TAG='1' order by oper_time desc ";
  public static String brand_SEL_BY_CLASSID = "SELECT * FROM brand_class where class_id=:VCLASS_ID order by oper_time desc";
  public static String brand_UPDATE_BY_ALL_SALES_CLASS = "update brand_class set class_name=:VCLASS_NAME,class_desc=:VCLASS_DESC,remove_tag=:VREMOVE_TAG,oper_user=:VOPER_USER,list_model=:VLIST_MODEL,index_model=:VINDEX_MODEL,content_model=:VCONTENT_MODEL,store_path=:VSTOCE_PATH,list_rule=:VLIST_RULE,home_name=:VHOME_NAME,article_rule=:VARTICLE_RULE,dyna_show=:VDYNA_SHOW where class_id=:VCLASS_ID";
  public static String brand_UPDATE_SALES_CLASS_NAME_BY_CLASS_ID = "update brand_class set class_name=:VCLASS_NAME where class_id=:VCLASS_ID";
  public static String brand_DELETE_BY_ALL_PRODUCT_CLASS = "delete  FROM brand_class where class_id=:VCLASS_ID";
  public static String brand_SELECT_SALES_BY_CLASS_ID = "select id from brand where class_id=:VCLASS_ID";
  public static String brand_SEL_SECOND_CLASS = "select class_id,class_name from brand_class where length(class_id)=length(:VLENGTH)";
  public static String brand_SEL_BY_SUN_CLASSID = "select class_id,class_name from brand_class where class_id like :VCLASS_ID and length(class_id) = :VlENGTH";
  public static String brand_SEL_BY_SUN_CLASSID_BRAND_NAME = "select class_id,class_name from brand_class where class_id like :VCLASS_ID and length(class_id) = :VlENGTH and CLASS_ID NOT IN(SELECT CLASS_ID FROM BRAND WHERE BRAND_NAME =:VBRAND_NAME)";
  public static String brand_INSERT_INTO_BRAND = "insert into brand (id,brand_name,class_id,business_model,brand_desc,brand_area,company_url,logo,commend,validity,cust_id,user_id,add_date,oper_time,contact_name,sort_no,recommend_brand,ppzsjmhztjlc) values(:VID,:VBRAND_NAME,:VCLASS_ID,:VBUSINESS_MODEL,:VBRAND_DESC,:VBRAND_AREA,:VCOMPANY_URL,:VLOGO,:VCOMMEND,:VVALIDITY,:VCUST_ID,:VUSER_ID,NOW(),NOW(),:VCONTACT_NAME,:VSORT_NO,:VRECOMMEND_BRAND,:VPPZSJMHZTJLC)";
  public static String brand_SELECT_BRAND_ADMIN_0 = "select brand_name,class_id from brand where brand_name like :VBRAND_NAME and class_id like :VCLASS_ID and (validity = 0 or validity = 2) group by brand_name order by OPER_TIME asc ";
  public static String brand_SELECT_BRAND_ADMIN_1 = "select brand_name,class_id from brand where brand_name like :VBRAND_NAME and class_id like :VCLASS_ID and (validity = 0 or validity = 1 or validity = 2) group by brand_name order by OPER_TIME asc ";
  public static String brand_SELECT_BRAND_ADMIN_CHECK = "select brand_name,class_id from brand where brand_name like :VBRAND_NAME and class_id like :VCLASS_ID and validity=0 group by brand_name order by SORT_NO desc,OPER_TIME desc ";
  public static String brand_SELECT_BRAND_COUNT_ADMIN_0 = "select class_id from brand where brand_name like :VBRAND_NAME and class_id like :VCLASS_ID and (validity = 0 or validity = 2) group by brand_name";
  public static String brand_SELECT_BRAND_COUNT_ADMIN_1 = "select class_id from brand where brand_name like :VBRAND_NAME and class_id like :VCLASS_ID and (validity = 0 or validity=1 or validity = 2) group by brand_name";
  public static String brand_SELECT_BRAND_COUNT_ADMIN_CHECK = "select class_id from brand where brand_name like :VBRAND_NAME and class_id like :VCLASS_ID and validity=0 group by brand_name";
  public static String brand_SELECT_BRAND = "select brand_name,class_id from brand where CUST_ID=:VCUST_ID and brand_name like :VBRAND_NAME and class_id like :VCLASS_ID group by brand_name order by SORT_NO desc,OPER_TIME desc ";
  public static String brand_SELECT_BRAND_COUNT = "select class_id from brand where CUST_ID=:VCUST_ID and brand_name like :VBRAND_NAME and class_id like :VCLASS_ID group by brand_name";
  public static String brand_SELECT_BRAND_LIST_ADMIN_1 = "select * from brand where brand_name = :VBRAND_NAME and class_id like :VCLASS_ID and (validity = 0 or validity = 1 or validity = 2) order by SORT_NO desc,OPER_TIME desc";
  public static String brand_SELECT_BRAND_LIST_ADMIN_0 = "select * from brand where brand_name = :VBRAND_NAME and class_id like :VCLASS_ID and (validity = 0 or validity = 2) order by SORT_NO desc,OPER_TIME desc";
  public static String brand_SELECT_BRAND_LIST_ADMIN_CHECK = "select * from brand where brand_name = :VBRAND_NAME and class_id like :VCLASS_ID and validity=0 order by SORT_NO desc,OPER_TIME desc";
  public static String brand_SELECT_BRAND_LIST = "select * from brand where CUST_ID =:VCUST_ID  AND brand_name = :VBRAND_NAME and class_id like :VCLASS_ID order by SORT_NO desc,OPER_TIME desc";
  public static String brand_DELETE_BRAND = "delete from brand where id = :VID";
  public static String brand_SELECT_BRAND_BY_ID = "select * from brand where id = :VID";
  public static String brand_SEL_BY_PRODUCTNAME = "select class_id,class_name from brand_class where SUBSTRING(:VCLASS_ID,1,6) = class_id or SUBSTRING(:VCLASS_ID,1,10) = class_id or SUBSTRING(:VCLASS_ID,1,15) = class_id or SUBSTRING(:VCLASS_ID,1,20) = class_id order by  class_id asc";
  public static String brand_UPDATE_BRAND_BY_ID = "update brand set brand_name=:VBRAND_NAME,business_model=:VBUSINESS_MODEL,class_id=:VCLASS_ID,brand_desc=:VBRAND_DESC,brand_area=:VBRAND_AREA,validity=:VVALIDITY,company_url=:VCOMPANY_URL,logo=:VLOGO,oper_time=NOW(),sort_no=:VSORT_NO,ppzsjmhztjlc=:VPPZSJMHZTJLC where id=:VID";
  public static String brand_SELECT_BRAND_CUST_ID = "select concat('/company/web/',cust_id,'/templates/index.html') as cust_page,cust_name from company where cust_state=1 and cust_type<>0  and cust_name like :VCUST_NAME";
  public static String brand_SELECT_BRAND_CUST_ID_COUNT = "select cust_id from company where cust_state=1 and cust_type<>0 and cust_name like :VCUST_NAME";
  public static String brand_CHECK_VALIDITY = "update validity=:VVALIDITY where id=:VID";
  public static String brand_SPARAM = "select * from site_parameter where param_name = 'brand_check' and param_value = 1";
  public static String brand_GET_BRAND_INFO_BY_CUSTID = "select b.id root_id,b.brand_name root_name,b.class_id,c.class_name from brand b,brand_class c where b.class_id = c.class_id and b.validity=1 and b.cust_id = :VCUST_ID";
  public static String brand_GET_BRAND_INFO_BY_CUSTID_KEY = "select b.id root_id,b.brand_name root_name,b.class_id,c.class_name from brand b,brand_class c where b.class_id = c.class_id and b.validity=1 and b.cust_id = :VCUST_ID and b.brand_name like :VKEYWORD";
  public static String brand_UPDATE_RECOMMEND_BRAND = "update brand set recommend_brand = 1 where id = :VID";
  public static String brand_SEL_AREA_BY_ID = "select * from area_info where area_id = substring(:VAREA_ID,1,3) or area_id = substring(:VAREA_ID,1,6) or area_id = substring(:VAREA_ID,1,10) or area_id = substring(:VAREA_ID,1,15)or area_id = substring(:VAREA_ID,1,20) ORDER BY AREA_ID ASC";
  public static String brand_GET_BRAND_NAME = "select brand_name from brand where id=:VID";
  public static String brand_SELECT_TUIJIAN_COMPANY_LIST = "select cust_name,add_date,cust_id from company where cust_type<>0 and commend='1' and display=1 and cust_state=1 order by add_date desc";
  public static String brand_SELECT_COMPANY_LIST_ALL = "SELECT distinct C.CUST_ID,C.CUST_NAME,c.cust_supply,c.company_scope,c.level_logo,a.area_name,c.logo_img,u.qq,u.msn from company c left join user_info u on c.cust_id = u.cust_id left join area_info a on a.area_id=c.eparchy_code  where substring(c.add_date,1,10) >=:VSTART_DATE AND ( substring(c.add_date,1,10) <=:VEND_DATE or :VEND_DATE ='' ) and c.eparchy_code like :VAREA_ID AND c.CUST_NAME LIKE :VCUST_NAME and c.cust_type=1 and c.cust_state=1 and c.type like :VTYPE AND C.CUST_SUPPLY LIKE :VCLASS_ID order by c.add_date desc ";
  public static String brand_SELECT_COMPANY_LIST = "SELECT distinct C.CUST_ID,C.CUST_NAME,c.cust_supply,c.company_scope,c.level_logo,a.area_name,c.logo_img,u.qq,u.msn from company c left join user_info u on c.cust_id = u.cust_id left join area_info a on a.area_id=c.eparchy_code  where c.add_date >=:VSTART_DATE AND ( C.ADD_DATE <=:VEND_DATE or :VEND_DATE ='' ) and c.eparchy_code like :VAREA_ID AND c.CUST_NAME LIKE :VCUST_NAME AND C.RECOMMEND_COMPANY=:VCOMMEND and c.cust_type<>0 and c.type like :VTYPE AND C.CUST_SUPPLY LIKE :VCLASS_ID order by c.oper_time desc ";
  public static String brand_SELECT_COMPANY_LIST_COUNT_ALL = "SELECT distinct C.CUST_ID from company c left join user_info u on c.cust_id = u.cust_id left join area_info a on a.area_id=c.eparchy_code  where substring(c.add_date,1,10) >=:VSTART_DATE AND ( substring(c.add_date,1,10) <=:VEND_DATE or :VEND_DATE ='' ) and c.eparchy_code like :VAREA_ID AND c.CUST_NAME LIKE :VCUST_NAME and c.cust_type=1 and c.cust_state=1 and c.type like :VTYPE AND C.CUST_SUPPLY LIKE :VCLASS_ID order by c.add_date desc ";
  public static String brand_SELECT_COMPANY_LIST_COUNT = "SELECT distinct C.CUST_ID from company c left join user_info u on c.cust_id = u.cust_id left join area_info a on a.area_id=c.eparchy_code  where c.add_date >=:VSTART_DATE AND ( C.ADD_DATE <=:VEND_DATE or :VEND_DATE ='' ) and c.eparchy_code like :VAREA_ID AND c.CUST_NAME LIKE :VCUST_NAME AND C.RECOMMEND_COMPANY=:VCOMMEND and c.cust_type<>0 and c.type like :VTYPE AND C.CUST_SUPPLY LIKE :VCLASS_ID order by c.add_date desc ";
  public static String brand_SELECT_CLASS_IDNAME = "SELECT CLASS_ID,CLASS_NAME FROM PRODUCT_CLASS ";
  public static String brand_SELECT_CLASS_SONCLASS = "SELECT pc.class_name,c.cust_name,c.cust_supply,count(pc.class_name) number,c.type,PC.CLASS_ID,pc.class_name FROM COMPANY C LEFT JOIN PRODUCT_CLASS PC ON C.CUST_SUPPLY LIKE concat('%',PC.CLASS_ID,'%') where c.cust_type <> 0 and c.type like :VTYPE and c.cust_supply <> '' and pc.class_id like :VCLASS_ID and length(pc.class_id)=:VLENGTH group by pc.class_name order by pc.class_name desc";
  public static String brand_SELECT_CLASS_LIST = "select class_name from brand_class where substr(:VCLASS_ID,1,3) = class_id or substr(:VCLASS_ID,1,6) = class_id or substr(:VCLASS_ID,1,10) = class_id or substr(:VCLASS_ID,1,15) = class_id or substr(:VCLASS_ID,1,20) = class_id ORDER BY CLASS_ID ASC";
  public static String brand_MOVE_SALES_CLASS_BY_ID = "update brand_class set class_id=:VCLASS_ID where class_id=:VUP_ID";
  public static String brand_SELECT_MAX_ID_BY_CLASS = "select max(class_id) as maxid from brand_class where length(class_id)=:VLENGTH and class_id like :VUP_ID";
  public static String brand_SELECT_SON_BY_UP_INCLUDE_SELF = "select class_id,class_name from brand_class where class_id like :VUP_ID";
  public static String brand_UPDATE_SALES_BY_CLASS_ID = "update brand set class_id=:VCLASS_ID where class_id=:VUP_ID";
  public static String brand_GET_AD_LIST = "select id,company_name,logo,url,cust_id from ad_image";
  public static String brand_GET_AD_LIST_COUNT = "select id from ad_image";
}

